﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using MySql.Data.MySqlClient;
using System.Data.SqlClient;

namespace DAL
{
    /// <summary>
    /// 数据库处理公用类
    /// </summary>
    public static class SqlHelper
    {
        public static readonly string connstr =
            ConfigurationManager.ConnectionStrings["connstr"].ToString();
        /// <summary>
        /// 查询返回datatable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql, params MySqlParameter[] parameters)
        {
            using (MySqlConnection ms = new MySqlConnection(connstr))
            {
                ms.Open();
                using (MySqlCommand cmd = ms.CreateCommand()) {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    MySqlDataAdapter ad = new MySqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    ad.Fill(ds);
                    return ds.Tables[0]; 
                };
            }

        }

        /// <summary>
        /// 查询返回datatable 报表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataTable ExecuteReportDataTable(string sql)
        {
            using (MySqlConnection ms = new MySqlConnection(connstr))
            {
                ms.Open();
                using (MySqlCommand cmd = ms.CreateCommand())
                {
                    cmd.CommandText = sql;
                    MySqlDataAdapter ad = new MySqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    ad.Fill(ds);
                    return ds.Tables[0];
                };
            }

        }

      
        /// <summary>
        /// 增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, params MySqlParameter[] parameter)
        {
           
            using(MySqlConnection ms = new MySqlConnection(connstr)){
                 
                ms.Open();
                using(MySqlCommand cmd =ms.CreateCommand()){
                    cmd.CommandText=sql;
                    cmd.Parameters.AddRange(parameter);
                    return cmd.ExecuteNonQuery();
                
                }
            }
        }
        /// <summary>
        /// 增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public static long ExecuteNonQueryId(string sql, params MySqlParameter[] parameter)
        {

            using (MySqlConnection ms = new MySqlConnection(connstr))
            {

                ms.Open();
                using (MySqlCommand cmd = ms.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameter);
                    cmd.ExecuteNonQuery();
                    return cmd.LastInsertedId;

                }
            }
        }
        /// <summary>
        /// 批量增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public static bool ExecuteNonQueryMore(List<string> ls)
        {
            bool flag = true;
            using (MySqlConnection ms = new MySqlConnection(connstr))
            {
                
                ms.Open();
                MySqlTransaction mt = ms.BeginTransaction();
                using (MySqlCommand cmd = ms.CreateCommand())
                {
                    try
                    {
                        foreach (string s in ls)
                        {
                            cmd.CommandText = s;
                            //cmd.Parameters.AddRange(parameter);
                            cmd.ExecuteNonQuery();
                        }
                        mt.Commit();
                    }
                    catch
                    {
                        flag = false;
                        mt.Rollback();
                    }
                    finally 
                    {
                        mt.Dispose();
                    }
                    return flag;

                }
            }
        }

        /// <summary>
        /// 查询单列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params MySqlParameter[] parameter)
        { 
            using(MySqlConnection ms = new MySqlConnection(connstr)){
                ms.Open();
                using(MySqlCommand cmd = ms.CreateCommand()){
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameter);
                    return cmd.ExecuteScalar();
                }
            }
        }

        public static int ExecuteMore(string sql) {

            using (MySqlConnection ms = new MySqlConnection(connstr))
            {
                ms.Open();
                using (MySqlCommand cmd = ms.CreateCommand())
                {
                        cmd.CommandText = sql;
                        return cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 转换成空字符串
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static object FromDbValue(object value) {
            if (value == DBNull.Value)
            {
                return null;
            }
            else {
                return value;
            }
        }
        /// <summary>
        /// 转换成数据库空字符串
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static object ToDbValue(object value) {
            if (value == null)
            {
                return DBNull.Value;
            }
            else {
                return value;
            }
        }

        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public static int BatchInsert(DataTable dt)  
        {

            using (MySqlConnection ms = new MySqlConnection(connstr))
            {

                ms.Open();
                using (MySqlCommand cmd = ms.CreateCommand())
                {
                    cmd.CommandText = GenBatchInserSql(dt);  
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        //生成批量插入的sql  
        private static string GenBatchInserSql(DataTable dt)
        {
            var names = string.Empty;
            var values = new StringBuilder();
            var types = new List<DbType>();
            var count = dt.Columns.Count;

            for (int i = 0; i < count; i++)
            {
                names += dt.Columns[i].ColumnName + ",";
                types.Add(GetDBType(dt.Columns[i].DataType));
            }
            names = names.TrimEnd(',');
            var n = 0;
            foreach (DataRow row in dt.Rows)
            {
                if (n > 0)
                {
                    values.Append(",");
                }
                values.Append("(");
                for (var j = 0; j < count; j++)
                {
                    if (j > 0)
                    {
                        values.Append(",");
                    }
                    var isstr = IsStrType(types[j]);
                    if (isstr)
                    {
                        values.AppendFormat("'{0}'", row[j]);
                    }
                    else
                    {
                        values.Append(row[j]);
                    }
                }
                values.Append(")");
                n++;
            }
            return string.Format("insert into {0} ({1}) values {2}", dt.TableName, names, values);
        }

        //判断某列是不是string类型  
        private static bool IsStrType(DbType dbType)
        {
            return dbType == DbType.AnsiString || dbType == DbType.AnsiStringFixedLength || dbType == DbType.String || dbType == DbType.StringFixedLength;
        }

        //根据datatype得到dbtype,将得到的结果作为参数传到上面判断某列是不是string类型的方法  
        private static DbType GetDBType(System.Type theType)
        {
            System.Data.SqlClient.SqlParameter p1;
            System.ComponentModel.TypeConverter tc;
            p1 = new System.Data.SqlClient.SqlParameter();
            tc = System.ComponentModel.TypeDescriptor.GetConverter(p1.DbType);
            if (tc.CanConvertFrom(theType))
            {
                p1.DbType = (DbType)tc.ConvertFrom(theType.Name);
            }
            else
            {
                try
                {
                    p1.DbType = (DbType)tc.ConvertFrom(theType.Name);
                }
                catch (Exception)
                {

                }
            }
            return p1.DbType;
        }
  

    }
}
